package com.junzhiit.nengji.dao.impl;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.bo.SystemUserBO;
import com.junzhiit.nengji.dao.SystemUserDAO;
import com.junzhiit.nengji.model.Function;
import com.junzhiit.nengji.model.SystemUser;

@Repository
public class SystemUserDAOImpl extends AbstractBaseDAO<SystemUser> implements SystemUserDAO {

	@Override
	public List<SystemUserBO> findAll(SystemUser systemUser, int pageNumber, int maxPageRow) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select");
		sql.append(" su.id,su.system_id,su.mobile_phone,su.password,su.email,su.name,su.gender,su.status,");
		sql.append(" su.is_main_account,su.created_time,su.is_delete,");
		sql.append(" s.name shop_name,");

		// subquery
		sql.append(" (select group_concat(r.name)");
		sql.append(" from role r");
		sql.append(" left join system_user_role sur on sur.role_id=r.id");
		sql.append(" where sur.system_user_id=su.id) role_name");

		sql.append(" from `system_user` su");
		sql.append(" left join `shop` s on s.id=su.system_id");
		sql.append(" where su.is_delete = ?");

		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());

		if (systemUser.getStatus() != null) {
			sql.append(" and su.status = ?");
			param.setInt(systemUser.getStatus().intValue());
		}
		if (StringUtils.isNotBlank(systemUser.getEmail())) {
			sql.append(" and su.email like ?");
			param.setString("%" + systemUser.getEmail() + "%");
		}
		if (StringUtils.isNotBlank(systemUser.getMobilePhone())) {
			sql.append(" and su.mobile_phone like ?");
			param.setString("%"+systemUser.getMobilePhone()+"%");
		}
		if (systemUser.getPlatform() != null) {
			sql.append(" and su.platform=?");
			param.setInt(systemUser.getPlatform());
		}
		if (StringUtils.isNotBlank(systemUser.getSystemId())) {
			sql.append(" and su.system_id=?");
			param.setString(systemUser.getSystemId());
		}

		if (systemUser.getIsMainAccount() != null) {
			sql.append(" and is_main_account=?");
			param.setInt(systemUser.getIsMainAccount());
		}

		if (systemUser.getSafetyLevel() != null) {
			sql.append(" and safety_level>=?");
			param.setInt(systemUser.getSafetyLevel());
		}
		sql.append(" order by su.system_id,is_main_account desc");

		return super.queryForPagingList(sql.toString(), SystemUserBO.class, param, pageNumber, maxPageRow);
	}

	@Override
	public int getTotalCount(SystemUser systemUser) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(su.id) from `system_user` su");
		sql.append(" left join `shop` s on s.id=su.system_id");
		sql.append(" where su.is_delete = ?");

		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());

		if (systemUser.getStatus() != null) {
			sql.append(" and su.status = ?");
			param.setInt(systemUser.getStatus().intValue());
		}
		if (StringUtils.isNotBlank(systemUser.getEmail())) {
			sql.append(" and su.email like ?");
			param.setString("%" + systemUser.getEmail() + "%");
		}
		if (StringUtils.isNotBlank(systemUser.getMobilePhone())) {
			sql.append(" and su.mobile_phone like ?");
			param.setString("%"+systemUser.getMobilePhone()+"%");
		}
		if (systemUser.getPlatform() != null) {
			sql.append(" and su.platform=?");
			param.setInt(systemUser.getPlatform());
		}
		if (StringUtils.isNotBlank(systemUser.getSystemId())) {
			sql.append(" and su.system_id=?");
			param.setString(systemUser.getSystemId());
		}
		if (systemUser.getIsMainAccount() != null) {
			sql.append(" and su.is_main_account=?");
			param.setInt(systemUser.getIsMainAccount());
		}
		if (systemUser.getSafetyLevel() != null) {
			sql.append(" and safety_level>=?");
			param.setInt(systemUser.getSafetyLevel());
		}
		return super.queryForInt(sql.toString(), param);
	}

	@Override
	public int getMobilePhoneCount(String mobilePhone) {
		String sql = "select count(id) from `system_user` where mobile_phone = ? and is_delete = ?";
		StatementParameter param = new StatementParameter();
		param.setString(mobilePhone);
		param.setInt(IsDelete.NO.toIntValue());
		return super.queryForInt(sql, param);
	}

	@Override
	public SystemUser findByMobilePhone(String mobilePhone) {
		String sql = "select * from `system_user` where mobile_phone = ?";
		StatementParameter param = new StatementParameter();
		param.setString(mobilePhone);
		return super.query(sql, SystemUser.class, param);
	}

	@Override
	public SystemUser login(String mobilePhone, String password) {
		StringBuilder sql = new StringBuilder();
		sql.append("select * from `system_user` where mobile_phone =? and password =? and is_delete=?");
		StatementParameter params = new StatementParameter();
		params.setString(mobilePhone);
		params.setString(password);
		params.setInt(IsDelete.NO.toIntValue());
		return super.query(sql.toString(), SystemUser.class, params);
	}

	@Override
	public List<SystemUser> getListBySystemId(String systemId) {
		String sql = "select * from `system_user` where system_id =? and is_delete =?";
		StatementParameter params = new StatementParameter();
		params.setString(systemId);
		params.setInt(IsDelete.NO.toIntValue());
		return super.queryForList(sql, SystemUser.class, params);
	}

	@Override
	public int checkEmail(String email) {
		String sql = "select count(id) from `system_user` where email =? and is_delete =?";
		StatementParameter params = new StatementParameter();
		params.setString(email);
		params.setInt(IsDelete.NO.toIntValue());
		return super.queryForInt(sql, params);
	}

	@Override
	public SystemUser findByEmail(String email) {
		String sql = "select * from `system_user` where email = ?";
		StatementParameter param = new StatementParameter();
		param.setString(email);
		return super.query(sql, SystemUser.class, param);
	}

	@Override
	public List<Function> getSystemUserFunctions(String systemUserId) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select");
		sql.append(" distinct f.*");
		sql.append(" from system_user_role sur");
		sql.append(" inner join role r on r.id=sur.role_id");
		sql.append(" inner join role_function rf on r.id=rf.role_id");
		sql.append(" inner join `function` f on f.id=rf.function_id");
		sql.append(" where sur.system_user_id=?");
		sql.append(" order by f.sort asc");

		StatementParameter params = new StatementParameter();
		params.setString(systemUserId);

		return super.queryForList(sql.toString(), Function.class, params);
	}

	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	@Override
	public boolean updateSystemUserStatus(String id, int status) {
		StringBuffer sql = new StringBuffer();
		sql.append(" update system_user set status=? where id=?");

		StatementParameter params = new StatementParameter();
		params.setInt(status);
		params.setString(id);

		return super.updateForBoolean(sql.toString(), params);

	}

	@Override
	public SystemUser findByShopsLocatedId(String shopsLocatedId) {
		String sql = "select * from `system_user` where is_delete = ? and shops_located_id = ?";
		StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(shopsLocatedId);
		return super.query(sql, SystemUser.class, param);
	}

	@Override
	public SystemUser findBySystemId(String systemId, int platform) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from system_user where system_id = ? and platform = ? ");
		
		StatementParameter params = new StatementParameter();
		params.setString(systemId);
		params.setInt(platform);
		
		return query(sql.toString(), SystemUser.class, params);
	}
}
